﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using Portal.Systems.Model;
using Portal.Database.Service;
using Portal.Web.Utility;
using Portal.Core;
using Portal.Utility;
using Portal.Systems.Bussiness.Template;
using System.Data.SqlClient;

namespace Portal.Systems.Bussiness
{
    public class RoleRelationTemplateProcessBll
    {
        public static RoleRelationTemplateCls CreateRoleRelationTemplateModel(
            string SiteId,
            string RoleRelationTemplateId)
        {
            SiteParam OSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId);
         
            string Query = "select * from TableRoleRelationTemplate where RoleRelationTemplateId=@RoleRelationTemplateId";
            DataTable dtResult= DBService.GetDataTable(null, Query, new DbParam[]{
                new DbParam("RoleRelationTemplateId",RoleRelationTemplateId)
            });
            if (dtResult.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                RoleRelationTemplateCls RoleRelationTemplateCls = new RoleRelationTemplateCls();
                RoleRelationTemplateCls.RoleRelationTemplateId = (string)dtResult.Rows[0]["RoleRelationTemplateId"];
                RoleRelationTemplateCls.RoleRelationTemplateName = (string)dtResult.Rows[0]["RoleRelationTemplateName"];
                try
                {
                    RoleRelationTemplateCls.SortIndex = (int)dtResult.Rows[0]["SortIndex"];
                }
                catch { }
                try
                {
                    RoleRelationTemplateCls.XmlData = (string)dtResult.Rows[0]["XmlData"];
                }
                catch { }
                try
                {
                    RoleRelationTemplateCls.AssetCode = (string)dtResult.Rows[0]["AssetCode"];
                }
                catch { }
                return RoleRelationTemplateCls;
            }
        }

        public static RoleRelationTemplateCls[] Reading(string SiteId, string AssetCode, string Keyword)
        {
            SiteParam OSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            string Query = "select *,RoleRelationTemplateInUser=(select count(*) from TableUsersInRoleRelationTemplate where fk_RoleRelationTemplate_Id=RoleRelationTemplateId) from TableRoleRelationTemplate where 1=1 ";
            if (Keyword.Trim().Length > 0)
            {
                Query += " and RoleRelationTemplateName like N'%" + Keyword + "%'";
            }

            if (AssetCode.Trim().Length > 0)
            {
                Query += " and AssetCode=@AssetCode";
            }
            Query += " order by RoleRelationTemplateName";

            SqlConnection sqlConnection = new SqlConnection(OSiteParam.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            if (AssetCode.Trim().Length > 0)
            {
                sqlCommand.Parameters.Add("@AssetCode", SqlDbType.VarChar);
                sqlCommand.Parameters["@AssetCode"].Value = AssetCode;
            }
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            RoleRelationTemplateCls[] RoleRelationTemplates = RoleRelationTemplateParser.ParseFromDataTable(dtResult);
            return RoleRelationTemplates;
        }

        public static RoleRelationTemplateCls[] ReadingRoleRelationTemplate(string SiteId, string Keyword)
        {
            SiteParam OSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            string Query = "select * from TableRoleRelationTemplate where 1=1 ";
            if (!string.IsNullOrEmpty(Keyword))
            {
                Query += " and RoleRelationTemplateName like N'%" + Keyword + "%'";
            }
            Query += " order by RoleRelationTemplateName";
            SqlConnection sqlConnection = new SqlConnection(OSiteParam.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            RoleRelationTemplateCls[] RoleRelationTemplates = RoleRelationTemplateParser.ParseFromDataTable(dtResult);
            return RoleRelationTemplates;
        }


        public static  RoleRelationTemplateCls[] ReadingForAssetCode(string SiteId, string AssetCode)
        {
            SiteParam OSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            string Query = "select * from TableRoleRelationTemplate where belongSite='" + OSiteParam.SiteData + "' ";
            if (!String.IsNullOrEmpty(AssetCode))
            {
                Query += " and AssetCode='" + AssetCode + "'";
            }
            Query+=" order by RoleRelationTemplateName";
            SqlConnection sqlConnection = new SqlConnection(OSiteParam.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            RoleRelationTemplateCls[] RoleRelationTemplates = RoleRelationTemplateParser.ParseFromDataTable(dtResult);
            return RoleRelationTemplates;
        }


        public static string AddRoleRelationTemplate(
            SqlTransaction sqlTransaction,
            string SiteId,
            string AssetCode,
            string RoleRelationTemplateName,
            int    SortIndex,
            string XmlData)
        {
            SiteParam OSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(OSiteParam.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string RoleRelationTemplateId = System.Guid.NewGuid().ToString();
            string Query = "Insert into TableRoleRelationTemplate(RoleRelationTemplateId, RoleRelationTemplateName, SortIndex, XmlData, belongSite,AssetCode) values (@RoleRelationTemplateId, @RoleRelationTemplateName, @SortIndex, @XmlData, @belongSite, @AssetCode)";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@RoleRelationTemplateId", SqlDbType.VarChar);
            sqlCommand.Parameters["@RoleRelationTemplateId"].Value = RoleRelationTemplateId;

            sqlCommand.Parameters.Add("@RoleRelationTemplateName", SqlDbType.NVarChar);
            sqlCommand.Parameters["@RoleRelationTemplateName"].Value = RoleRelationTemplateName;

            sqlCommand.Parameters.Add("@SortIndex", SqlDbType.Int);
            sqlCommand.Parameters["@SortIndex"].Value = SortIndex;

            sqlCommand.Parameters.Add("@XmlData", SqlDbType.NVarChar);
            sqlCommand.Parameters["@XmlData"].Value = XmlData;

            sqlCommand.Parameters.Add("@belongSite", SqlDbType.VarChar);
            sqlCommand.Parameters["@belongSite"].Value = OSiteParam.SiteData;

            sqlCommand.Parameters.Add("@AssetCode", SqlDbType.VarChar);
            sqlCommand.Parameters["@AssetCode"].Value = AssetCode;


            DataSet ds = new DataSet();
            ds.ReadXml(new StringReader(XmlData));

            try
            {
                sqlCommand.ExecuteNonQuery();
                sqlCommand.Parameters.Clear();

                for (int iIndex = 0; iIndex < ds.Tables[0].Rows.Count; iIndex++)
                {
                    string Id = (string)ds.Tables[0].Rows[iIndex]["Id"];
                    string FromRoleId = (string)ds.Tables[0].Rows[iIndex]["FromRoleId"];
                    string ToRoleId = (string)ds.Tables[0].Rows[iIndex]["ToRoleId"];
                    Query = "Insert into TableRoleRelationTemplateItem(RoleRelationTemplateItemId, frkRoleRelationTemplateId, frkParentRoleId, frkChildRoleId) values (@RoleRelationTemplateItemId, @frkRoleRelationTemplateId, @frkParentRoleId, @frkChildRoleId)";
                    sqlCommand.CommandText = Query;

                    sqlCommand.Parameters.Clear();
                    sqlCommand.Parameters.Add("@RoleRelationTemplateItemId", SqlDbType.VarChar);
                    sqlCommand.Parameters["@RoleRelationTemplateItemId"].Value = Id;

                    sqlCommand.Parameters.Add("@frkRoleRelationTemplateId", SqlDbType.VarChar);
                    sqlCommand.Parameters["@frkRoleRelationTemplateId"].Value = RoleRelationTemplateId;

                    sqlCommand.Parameters.Add("@frkParentRoleId", SqlDbType.VarChar);
                    sqlCommand.Parameters["@frkParentRoleId"].Value = FromRoleId;

                    sqlCommand.Parameters.Add("@frkChildRoleId", SqlDbType.VarChar);
                    sqlCommand.Parameters["@frkChildRoleId"].Value = ToRoleId;

                    sqlCommand.ExecuteNonQuery();
                }
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
            return RoleRelationTemplateId;
        }


        public static void SaveRoleRelationTemplate(
            SqlTransaction sqlTransaction,
            string SiteId,
            string RoleRelationTemplateId,
            string RoleRelationTemplateName,
            string XmlData)
        {
            SiteParam OSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(OSiteParam.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string Query = "Update TableRoleRelationTemplate set RoleRelationTemplateName=@RoleRelationTemplateName, XmlData=@XmlData where RoleRelationTemplateId=@RoleRelationTemplateId";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@RoleRelationTemplateId", SqlDbType.VarChar);
            sqlCommand.Parameters["@RoleRelationTemplateId"].Value = RoleRelationTemplateId;

            sqlCommand.Parameters.Add("@RoleRelationTemplateName", SqlDbType.NVarChar);
            sqlCommand.Parameters["@RoleRelationTemplateName"].Value = RoleRelationTemplateName;

            sqlCommand.Parameters.Add("@XmlData", SqlDbType.NVarChar);
            sqlCommand.Parameters["@XmlData"].Value = XmlData;


            DataSet ds = new DataSet();
            ds.ReadXml(new StringReader(XmlData));
            try
            {
                sqlCommand.ExecuteNonQuery();
                sqlCommand.Parameters.Clear();
                sqlCommand.CommandText = "delete from TableRoleRelationTemplateItem where frkRoleRelationTemplateId='" + RoleRelationTemplateId + "'";
                sqlCommand.ExecuteNonQuery();
                sqlCommand.Parameters.Clear();
                for (int iIndex = 0; iIndex < ds.Tables[0].Rows.Count; iIndex++)
                {
                    string Id = (string)ds.Tables[0].Rows[iIndex]["Id"];
                    string FromRoleId = (string)ds.Tables[0].Rows[iIndex]["FromRoleId"];
                    string ToRoleId = (string)ds.Tables[0].Rows[iIndex]["ToRoleId"];
                    Query = "Insert into TableRoleRelationTemplateItem(RoleRelationTemplateItemId, frkRoleRelationTemplateId, frkParentRoleId, frkChildRoleId) values (@RoleRelationTemplateItemId, @frkRoleRelationTemplateId, @frkParentRoleId, @frkChildRoleId)";
                    sqlCommand.CommandText = Query;

                    sqlCommand.Parameters.Clear();
                    sqlCommand.Parameters.Add("@RoleRelationTemplateItemId", SqlDbType.VarChar);
                    sqlCommand.Parameters["@RoleRelationTemplateItemId"].Value = Id;

                    sqlCommand.Parameters.Add("@frkRoleRelationTemplateId", SqlDbType.VarChar);
                    sqlCommand.Parameters["@frkRoleRelationTemplateId"].Value = RoleRelationTemplateId;

                    sqlCommand.Parameters.Add("@frkParentRoleId", SqlDbType.VarChar);
                    sqlCommand.Parameters["@frkParentRoleId"].Value = FromRoleId;

                    sqlCommand.Parameters.Add("@frkChildRoleId", SqlDbType.VarChar);
                    sqlCommand.Parameters["@frkChildRoleId"].Value = ToRoleId;

                    sqlCommand.ExecuteNonQuery();
                }
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }

        public static void DeleteRoleRelationTemplate(
            SqlTransaction sqlTransaction, 
            string SiteId,
            string RoleRelationTemplateId)
        {
            SiteParam OSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(OSiteParam.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string Query =
                    " Delete from TableRoleRelationTemplateItem where frkRoleRelationTemplateId=@RoleRelationTemplateId;" +
                    " Delete from TableRoleRelationTemplate where RoleRelationTemplateId=@RoleRelationTemplateId";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@RoleRelationTemplateId", SqlDbType.VarChar);
            sqlCommand.Parameters["@RoleRelationTemplateId"].Value = RoleRelationTemplateId;

            try
            {
                sqlCommand.ExecuteNonQuery();
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }
    }
}
